from datetime import timedelta, datetime
import re

from sqlalchemy import and_

import pyecharts.options as opts
from connection_db.connection_mssql import getDBSession
from connection_db.model import FundUseTransactions
from connection_db.model.FundUseTransactions import FundTransaction
from example.commons import Collector, Faker
from pyecharts.charts import Page, Pie
from py_linq import Enumerable
import pandas as pd

# from connection_db.model.FundUseTransactions import FundUseTransactions


# # 筛选
# 根据主键获取print query.filter(User.user_name == 2)  # 只显示sql语句，不会执行查询print query.filter(User.user_name == 'lujianxing').all()  # 执行查询print query.filter(User.user_name == 'lujianxing', User.accout == 1245678, User.age > 10).all()  # 执行查询print query.filter(User.user_name == 'lujianxing').filter(User.accout == 1245678).all()
# user = query.get(1)

# 执行查询print query.filter("user_name = 'lujianxing' and accout=1245678").all()  # 执行查询
# print query.filter("user_name = 'lujianxing'").all()
# query2 = session.query(User.user_name)  # 返回的结果不是User的实例，而是元组print query2.all()  # 执行查询print query2.offset(1).limit(1).all()  # 等于 limit 1,1# 排序print query2.order_by(User.user_name).all()
# print query2.order_by('user_name').all()
# print query2.order_by(User.user_name.desc()).all()
# print query2.order_by(User.user_name, User.accout.desc()).all()
# print query2.filter("user_name = 'lujianxing' and accout=1245678").count()
# # 聚合查询print session.query(func.count('*')).select_from(User).scalar()
# print session.query(func.count('1')).select_from(User).scalar()
# print session.query(func.count(User.id)).scalar()
# print session.query(func.count('*')).filter(User.id > 0).scalar() # filter() 中包含 User，因此不需要指定表print session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1 # 可以用 limit() 限制 count() 的返回数print session.query(func.sum(User.id)).scalar()
# print session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该数据库支持print session.query(func.current_timestamp()).scalar()
# print session.query(func.md5(User.name)).filter(User.id == 1).scalar()


C = Collector()

# @C.funcs
# def line_base() -> Line:
#     session = getDBSession()
#     query = session.query(FundDaily)
#
#     users = query.filter(FundDaily.code == '519915').order_by(FundDaily.GetTime.asc()).all()
#     # userc = Enumerable(users).order_by(FundDaily.GetTime.asc())
#     # userc = Enumerable(users).select(lambda x: {x.GetTime, x.NetValue}).to_list()
#     name = Enumerable(users).first().FundName  # Enumerable(users).first()
#
#     c = (
#         Line()
#
#             .add_xaxis(Enumerable(users).select(lambda x: x.GetTime).to_list())
#             .add_yaxis(name, y_axis=Enumerable(users).select(lambda x: x.NetValue)
#                        , markpoint_opts=opts.MarkPointOpts(
#                 data=[opts.MarkPointItem(type_="max"), opts.MarkPointItem(type_="min")])
#                        , markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average")])
#                        # .add_yaxis("商家B", Enumerable(userc).select(lambda x: x.NetValue))
#                        )
#             .set_global_opts(title_opts=opts.TitleOpts(title="")) #title="Line-基本示例")
#         # .options()
#     )
#
#     return c
from datetime import date, timedelta

'''
指定某一时间段 code 返回其交易数据
'''


@C.funcs
def pie_base() -> Pie:
    session = getDBSession()
    query = session.query(FundTransaction)

    users = query.filter(
        and_(FundTransaction.UserId == 4, FundTransaction.AddTime > datetime.now() + timedelta(days=-60))).all()
    ##(Object.created_date+timedelta(days=1))>datetime.now())
    df = pd.DataFrame(users)
    # userc = Enumerable(users).order_by(FundDaily.GetTime.asc())
    # userc = Enumerable(users).select(lambda x: {x.GetTime, x.NetValue}).to_list()
    # name = Enumerable(users).first().FundName  # Enumerable(users).first()
    abc = [];
    items = Enumerable(users).select(lambda x: x.FundName).distinct().to_list()
    # abc = Enumerable(items).select(lambda x: {x, Enumerable(users).where(lambda x: x.FundName == x).count()}).to_list();
    for z in items:
        ttt = {}
        ttt["k"] = z;
        ttt["v"] = Enumerable(users).where(lambda x: x.FundName == z).count()
        abc.append(Enumerable(users).where(lambda x: x.FundName == z).count())
        # abc.append({z,Enumerable(users).where(lambda x: x.FundName == z).count()})
    efg = [list(z) for z in zip(items, abc)]
    dddd = Enumerable(efg).order_by_descending(lambda x: x[1]).to_list();
    # for xx in abc:
    #     number = re.compile('^[0-9]*$').match(xx["k"])
    #     if number:
    #         efg.append({xx["v"], xx["k"]})
    #     else:
    #         efg.append({xx["k"], xx["v"]})

    c = (
        Pie()
            # .add("", [list(z) for z in zip(Faker.choose(), Faker.values())])
            .add("", dddd)
            .set_global_opts(title_opts=opts.TitleOpts(title=""), legend_opts=opts.LegendOpts(
            orient="vertical", pos_top="10%", pos_left="2%",
        ), )
            .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
    )
    c.width = '1200px';
    c.height = '680px';
    return c


Page().add(*[fn() for fn, _ in C.charts]).render()
print('ok')
